package com.mengtian_tech.tju.dataquery.api.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelReadTool {
	/**
	 * 04 -- 377536
	 * @param fileName
	 * @return
	 * @throws Exception
	 */
	public static  List<Map<String,Object>> readExcel(String fileName)throws Exception{
		
		FileWriter w = new FileWriter("d:/test/06.sql");
		
		List<Map<String,Object>> result = new ArrayList<Map<String,Object>>();
		XSSFWorkbook wb =new XSSFWorkbook(new FileInputStream(new File(fileName)));
		
		XSSFSheet sheet = wb.getSheetAt(0);
		
		int last = sheet.getLastRowNum();
		for(int i=1;i<= last;i++) {
			XSSFRow r = sheet.getRow(i);
			String voucherNo = r.getCell(0).getStringCellValue();
			String inTime = r.getCell(1).getStringCellValue();
			String outTime = "";
			if(r.getCell(2) != null) {
				outTime = r.getCell(2).toString();
			}
			String str = " INSERT INTO `data_ai`.`temp_car_park` (  `voucher_no`, `in_time`, `out_time`) VALUES (  '"+
					voucherNo.trim()+"'";
			
			if(inTime.trim().equals("")) {
				str +=" ,null ";
			}else {
				str +=" ,'"+inTime.trim()+"'";
			}
			
			
			if(outTime.equals("")) {
				str += ", null);\r\n";
			}else {
				str += ", '"+outTime.trim()+"');\r\n";
			}
			
			
			w.write(str);
			
		}
		
		wb.close();
		w.close();
		return result;
		
	}
	
	public static void main(String args[]) throws Exception {
		readExcel("C:\\Users\\wxd56\\Desktop\\车辆\\06.xlsx");
	}
}
